package com.fpno3.blog;

import com.fpno.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/*
* 使用preparedStatement实现批量数据的操作
* update delete本身具有批量操作的效果
* 批量操作主要指批量insert
* 题目：向goods表中插入20000条数据
*CREATE TABLE goods(
id INT PRIMARY KEY auto_increment,
NAME VARCHAR(25)
)
* 方式一：使用statement
*
* */
public class InsertTest {
    //批量插入的方式二 好事14111ms
    @Test
    public void testInsert()throws Exception{
        Connection connection = JDBCUtils.getConnection();
        String sql="insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i=1;i<=20000;i++){
            ps.setObject(1,"name_"+i);
            System.out.println("第"+i+"次插入");
            ps.execute();
        }
        long last = System.currentTimeMillis();
        System.out.println("花费的时间为"+(last-start));
        JDBCUtils.closeResource(connection,ps);
    }

    /*批量插入的方式三
    * 1.addBatch() executeBatch() clearBatch()
    * mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
    * 		 ?rewriteBatchedStatements=true 写在配置文件的url后面
    *
    * 时间：154ms
    * */
    @Test
    public void testInsert2()throws Exception{
        Connection connection = JDBCUtils.getConnection();
        String sql="insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i=1;i<=20000;i++){
            ps.setObject(1,"name_"+i);

            //1 攒 sql
            ps.addBatch();
            if (i%500==0){
                //2 执行 batch
                ps.executeBatch();

                //3 清空batch
                ps.clearBatch();
            }
        }
        long last = System.currentTimeMillis();
        System.out.println("花费的时间为"+(last-start));
        JDBCUtils.closeResource(connection,ps);
    }

    @Test
    public void testInsert3()throws Exception{
        Connection connection = JDBCUtils.getConnection();

        //设置不允许自动提交数据
        connection.setAutoCommit(false);

        String sql="insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i=1;i<=1000000;i++){
            ps.setObject(1,"name_"+i);

            //1 攒 sql
            ps.addBatch();
            if (i%500==0){
                //2 执行 batch
                ps.executeBatch();

                //3 清空batch
                ps.clearBatch();
            }
        }
        connection.commit();
        long last = System.currentTimeMillis();
        System.out.println("花费的时间为"+(last-start));
        JDBCUtils.closeResource(connection,ps);
    }
}
